import pymysql
import urllib
from urllib.request import urlopen
import requests
import json
import pprint
def connection():
    dbconn = pymysql.connect(
        host="127.0.0.1",
        database="test",
        user="root",
        password="",
        port=3306,
        charset='utf8'
    )
    return dbconn
def list_col():
    db = connection()
    cursor = db.cursor()
    sql = "SELECT orderid,bourn,SUM(area*coefficient) as volumes,createdate FROM all_data WHERE createdate = '2017-06-02'  GROUP BY orderid,bourn,createdate ORDER  BY volumes DESC "
    cursor.execute(sql)
    myresult = cursor.fetchall()
    db.close()
    return myresult
def delete():
    db = connection()
    cursor = db.cursor()
    sql = "delete from address_copy1_copy"
    hql = "delete from analysisdata_copy1_copy"
    cursor.execute(sql)
    cursor.execute(hql)
    db.close()
def inser_ma(matrix):
    db = connection()
    cursor = db.cursor()
    # sql = "INSERT INTO address_copy1_copy ( bourn,lat,lng ) VALUES ( '%s', '%s', '%s' )"% (reslut_dist['bourn'],reslut_dist['lat'],reslut_dist['lng'])
    hql = "INSERT INTO analysisdata_copy1_copy ( orderid,bourn,volume,createdate ) VALUES ('%s', '%s','%s','%s' )" % (matrix['freightId'],matrix['bourn'],matrix['volume'],matrix['time'])
    # cursor.execute(sql)
    cursor.execute(hql)
    db.close()
def inser_re(reslut_dist):
    db = connection()
    cursor = db.cursor()
    sql = "INSERT INTO address_copy1_copy ( bourn,lat,lng ) VALUES ( '%s', '%s', '%s' )"% (reslut_dist['bourn'],reslut_dist['lat'],reslut_dist['lng'])
    # hql = "INSERT INTO analysisdata_copy1_copy ( orderid,bourn,volume,createdate ) VALUES ('%s', '%s','%s','%s' )" % (matrix['freightId'],matrix['bourn'],matrix['volume'],matrix['time'])
    cursor.execute(sql)
    # cursor.execute(hql)
    db.close()
def getlnglat(value):
    url = 'http://api.map.baidu.com/geocoder/v2/?address=%s&output=json&ak=7IX1CKV9GNtcq0HywgTDY2f8Y67bHgOC'%value
    res = requests.get(url)
    json_data = json.loads(res.text)
    if json_data["status"] == 0:
        lat_lng = {}
        # print(json_data)
        lat_lng["bourn"] = value
        lat_lng["lat"] = json_data['result']['location']['lat']
        lat_lng["lng"] = json_data['result']['location']['lng']
        return lat_lng
def getStartData():
    dbconn=connection()
    try:
        with dbconn.cursor() as cursor:
            sql = "select *  from address "
            cursor.execute(sql)
            data = cursor.fetchall()
    finally:
        dbconn.close()
    return data

delete()
reslut = list_col()
matrix = []
reslut_dist = []
address_tab = []
for i in range(len(reslut)):
    trans_info = "loc"+str(i)
    trans_info = {}
    trans_info["freightId"] = reslut[i][0]
    trans_info["bourn"] = reslut[i][1]
    trans_info["time"] = reslut[i][3]
    trans_info["volume"] = abs(reslut[i][2])
    matrix.insert(i,trans_info)
# print(matrix)
for i in range(len(matrix)):
    if matrix[i]["bourn"] not in reslut_dist:
        reslut_dist.append(matrix[i]["bourn"])
for i in range(len(reslut_dist)):
    lng_lat = "lng_lat"+str(i)
    
    lng_lat = getlnglat(reslut_dist[i])
    print(lng_lat)
    address_tab.insert(i,lng_lat)

for i in range(len(matrix)):
    if matrix[i]is not None:
        inser_ma(matrix[i])
for i in range(len(address_tab)):
    if address_tab[i]is not None:
        inser_re(address_tab[i])    

